import openpyxl
import sys
#
# 根据配置信息生成操作对象
# 定义要执行的sql %s为占位符
#sql = "INSERT INTO model_index_unit(table_name, field_id, field_tb, field_hb, field_name, field_unit)  VALUES ('%s','%s','%s','%s','%s','%s');"
sql = "INSERT INTO dataset_nlp_prompt_dict(";
workbook = openpyxl.load_workbook('data.xlsx')

sheet = workbook["Sheet6"]
#sheet = workbook["hb_ai_pre_table"]

count = 0
for one_column_data in sheet.iter_rows():
    if count == 0:
        count+=1
        #处理表头
        st,sa=0,0
        for cell in one_column_data:
            if st==0:
                sql += cell.value
            else:
                sql += "," + cell.value
            st+=1;
        sql += ")  VALUES ("
        for cell in one_column_data:
            if sa==0:
                sql += "%s"
            else:
                sql += "," +"%s"
            sa+=1;
        sql+=");"
        print(sql)
    else:
        row_data = []
        for cell in one_column_data:
            if cell.value is None:
                row_data.append('null')
            else:
                row_data.append("'"+str(cell.value)+"'")
            # print(cell.value)
        val = tuple(row_data)
        #print(val)
        try:
            print(sql%val)
        except :
            print("Unexpected error:", sys.exc_info())
    #print(val)
